var express = require('express');
var fs = require('fs');
var path = require('path');
var router = express.Router();
var session = require('express-session');
router.use(session({ secret: 'keyboard cat', resave: false, saveUninitialized: true, cookie: { maxAge: 60000 } }));

// 导入MySQL模块
var mysql = require('mysql');

var dbConfig = {
    mysql: {
        host: '192.168.2.200',
        user: 'root',
        port: '4406',
        password: '123456',
        database: 'expressdb'
    }
}

var userSQL = {
    findUser: 'SELECT * FROM user WHERE name = ?',
    insert: 'INSERT INTO user(name,password) VALUES(?,?)',
    queryAll: 'SELECT * FROM user',
    getUserById: 'SELECT * FROM user WHERE name = ? ',
    test: 'SELECT * FROM user',
    login: 'SELECT (name,password) FROM user WHERE VALUES(?,?) '
}

var blogSQL = {
    //查询博客下的评论条数和点赞条数
    findAllBlogAndCommit: 'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blog.showIndex = 1 order by creat_time DESC LIMIT ?,?',
    findAllBlog: 'SELECT `blog`.* ,`blog_class`.className FROM `blog` LEFT JOIN `blog_class` ON `blog`.blogId = `blog_class`.blogId',//多表关联查询 左连接 查询包含每条博客的标签
    findAllBlogByclassName: 'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blog.showIndex = 1 and className = ?order by creat_time DESC LIMIT ?,?',
    addBlog: 'INSERT INTO blog(userName,title,body,className,creat_time,update_time) VALUES(?,?,?,?,?,?)',
    updateBlog: 'UPDATE blog SET title = ?,body = ?,className=?,update_time=? WHERE blogId = ?',
    deleteBlog: 'DELETE FROM blog WHERE blogId = ?',
    findBlogByUsername: 'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog WHERE userName = ? and blog.showIndex = 1 order by update_time DESC',
    addBlogClass: 'UPDATE blog SET className = ? WHERE blogId = ?',
    findBlogClass: 'SELECT DISTINCT className FROM `blog` where showIndex = 1',
    findBlogByClassName: 'SELECT * FROM blog WHERE className = ?',
    findBlogCommitByBlogId: 'SELECT * from user as a LEFT JOIN blog_commit as b on a.name = b.commitName where blogId = ? order by update_time DESC',
    findBlogByBlogId: 'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blogId = ?',
    addCommit: 'INSERT INTO blog_commit(commitName,blogId,commitBody,avatar,creat_time) VALUES (?,?,?,?,?)',
    changeHeadImg: 'UPDATE user SET headImg = ? WHERE `user`.name = ? and `user`.`password`= ?',
    //查找所有博客评论条数 :'select blog.* , (select count(id) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCountfrom blogorder by blog.blogId)'
    updatedUserInfo: 'UPDATE user SET nickName = ?,info = ?,sex = ? WHERE name= ? and password = ?',
    findFabByUsername: 'select * from blog as a LEFT JOIN blog_fabulous as b on a.blogId = b.blogId where commitName = ?'
}

// 使用DBConfig.js的配置信息创建一个MySQL连接池
var pool = mysql.createPool(dbConfig.mysql);
// 响应一个JSON数据

router.get('/', function (req, res, next) {
    res.render('index', { title: 'Express' });
});

//注册接口
router.post('/register', function (req, res, next) {//name,password
    // 获取前台页面传过来的参数  
    var param = req.body;
    // 建立连接 增加一个用户信息 
    pool.getConnection(function (err, connection) {
        connection.query(userSQL.findUser, [param.name], function (err, result) {
            // 以json形式，把操作结果返回给前台页面     
            if (result == false) {
                connection.query(userSQL.insert, [param.name, param.password], function (err, result) {
                    if (result) {
                        result = {
                            code: 200,
                            msg: '增加成功'
                        };
                    }
                    // 以json形式，把操作结果返回给前台页面     
                    res.send({ success: true, data: result });
                    connection.release()
                    // 释放连接 如果不释放连接超过数据库最大连接数量就会卡死
                });
            } else {
                res.send({ success: false, errorMsg: '该用户存在表中' });
                connection.release()
            }
        })
    })
})

//登录
router.post('/login', function (req, res, next) {//name password
    // 获取前台页面传过来的参数  req.query
    var param = req.body;
    console.log(req.body)
    // 建立连接 增加一个用户信息 
    pool.getConnection(function (err, connection) {
        connection.query(userSQL.findUser, [param.name], function (err, result) {
            // 以json形式，把操作结果返回给前台页面 
            console.log(result)
            if (result == false) { //数组为空
                res.send({ success: false, errorMsg: '该用户未注册' });
                connection.release()
            } else {
                if (result[0].password == param.password) {
                    req.session.userInfo = result[0].name
                    res.send({ success: true, data: '登录成功', userInfo: result[0] })
                    connection.release()
                } else {
                    res.send({ success: false, data: '用户名或密码错误' })
                    connection.release()
                }
            }
        })
    })
})

//检查是否登录
router.get('/islogin', function (req, res, next) {//name
    // 获取前台页面传过来的参数  
    var param = req.query;
    // 建立连接 增加一个用户信息 
    console.log(req.session.userInfo)
    if (req.session.userInfo == param.name) {
        res.send({ success: true, data: req.session.userInfo, msg: '已登录' });
    } else {
        res.send({ success: false, data: '未登录' });

    }
})

//注销
router.post('/logout', function (req, res, next) {
    // 获取前台页面传过来的参数  
    var param = req.query;
    req.session.destroy();
    res.send({ success: true, data: '注销成功' });
    connection.release()
})

//添加博客
router.post('/addBlog', function (req, res, next) {//name,title,body
    var param = req.body;
    var date = new Date()
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.addBlog, [param.name, param.title, param.body, param.className, date, date], function (err, result) {
            if (err) {
                res.send({ success: true, data: err, msg: '添加失败' });
                connection.release()
            }
            res.send({ success: true, data: '添加成功' });
            connection.release()
        })
    })
})

//删除博客
router.post('/deleteBlog', function (req, res, next) { //blogId
    var param = req.query;
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.deleteBlog, [param.blogId], function (err, result) {
            if (err) {
                res.send({ success: true, data: err, msg: '添加失败' });
                connection.release()
            }
            res.send({ success: true, data: '删除成功' });
            connection.release()
        })
    })
})

//更改博客
router.post('/updateBlog', function (req, res, next) { //title,body,blogId
    var param = req.body;
    console.log(param.update_time)
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.updateBlog, [param.title, param.body, param.className, param.update_time, param.blogId], function (err, result) {
            if (err) {
                res.send({ success: true, data: err, msg: '添加失败' });
                connection.release()
            }
            res.send({ success: true, data: '更改成功' });
            connection.release()
        })
    })
})

//查某人的博客
router.get('/findBlogByUsername', function (req, res, next) { //username
    var param = req.query;
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findBlogByUsername, [param.userName], function (err, result) {
            if (result == false) {
                res.send({ success: false, data: '', errorMsg: '无博客' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})

//添加博客的分类名
router.post('/addBlogClass', function (req, res, next) { //username
    var param = req.query;
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.addBlogClass, [param.className, param.blogId], function (err, result) {
            if (err) {
                res.send({ success: true, data: err, msg: '添加失败' });
                connection.release()
            } else {
                res.send({ success: true, data: '添加成功' });
                connection.release()
            }

        })
    })
})

//查找所有博客(包括评论条数和点赞数)
router.get('/findAllBlogAndCommit', function (req, res, next) { //username
    var param = req.query;
    var row = parseInt(param.row)
    var page = (param.page - 1) * row
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findAllBlogAndCommit, [page, row], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查询失败' });
                connection.release()
            } else {
                connection.query('SELECT COUNT(blogId) from blog where showIndex = 1', function (err, result2) {
                    res.send({ success: true, data: result, total: result2[0]['COUNT(blogId)'] });
                    connection.release()
                })
            }
        })
    })
})

//查找同一分类的博客
router.get('/findAllBlogByclassName', function (req, res, next) { //className page row
    var param = req.query;
    var row = parseInt(param.row)
    var page = (param.page - 1) * row
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findAllBlogByclassName, [param.className, page, row], function (err, result) {
            res.send({ success: true, data: result, total: result.length });
            connection.release()
        })
    })
})


//通过id查找博客
router.get('/findBlogByBlogId', function (req, res, next) { //username
    var param = req.query;
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findBlogByBlogId, [param.blogId], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查找' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})
//查找所有博客分类
router.get('/findBlogClass', function (req, res, next) { //username
    var param = req.query;
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findBlogClass, function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查找失败' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})
//查找博客分类下的所有博客
router.post('/findBlogByClassName', function (req, res, next) { //username
    var param = req.body;//放到body里面
    console.log(param, req.body)
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findBlogByClassName, [param.className], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查找失败' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})
//增加阅读量 含有过滤ip
router.post('/addReadCount', function (req, res, next) { //blogId
    var param = req.body;//放到body里面
    console.log(param, req.body)
    let getClientIp = function (req) {
        return req.headers['x-forwarded-for'] || req.connection.remoteAddress || req.socket.remoteAddress || req.connection.socket.remoteAddress || '';
    };

    console.log(getClientIp(req));
    let ip = getClientIp(req).match(/\d+.\d+.\d+.\d+/);
    console.log(ip);
    ip = ip ? ip.join('.') : null;
    console.log(ip);


    pool.getConnection(function (err, connection) {
        connection.query("SElECT ip FROM blog_readIp WHERE blogId = ?", [param.blogId], function (err, result) {
            var exit = false
            console.log(result)
            if (result.length == 0) {
                exit = false
            } else {
                result.forEach(item => {
                    if (item.ip == ip) {
                        exit = true
                    } else {
                        exit = false
                    }
                })
            }

            if (exit) {
                res.send({ success: false, data: err, msg: '改博客已被改ip查看过一次' });
                connection.release()
            } else {
                connection.query("INSERT INTO blog_readIp(ip,blogId) VALUES (?,?)", [ip, param.blogId], function (err, result1) {
                    if (err) {
                        //console.log('插入失败11111111111');
                        res.send({ success: false, data: err, msg: '插入失败1' });
                        connection.release()
                    } else {
                        connection.query("UPDATE blog SET readcount = readcount+1 WHERE blogId = ?", [param.blogId], function (err, result2) {
                            if (err) {
                                //console.log('插入失败222222222');
                                res.send({ success: false, data: err, msg: '插入失败2' });
                                connection.release()
                            } else {
                                //console.log('插入成功!!!!!!!!!!!!!!');
                                res.send({ success: true, data: result2, msg: '插入成功' });
                                connection.release()
                            }
                        })
                    }
                })
            }
        })
    })
})

//增加点赞数 含有过滤用户 参数blogId userName
router.post('/addFabulous', function (req, res, next) { //username
    var param = req.body;//放到body里面
    pool.getConnection(function (err, connection) {
        connection.query('select blogId from blog_fabulous where commitName = ?', [param.userName], function (err, result) {
            console.log(result, result == false)
            var exit
            result.forEach(item => {
                console.log(item.blogId)
                if (item.blogId == param.blogId) {
                    exit = true

                } else {
                    exit = false
                }
            })
            if (!exit) {
                connection.query('INSERT INTO blog_fabulous(commitName,blogId) VALUES(?,?)', [param.userName, param.blogId], function (err, result) {
                    if (err) {
                        res.send({ success: false, data: err, msg: '点赞失败2' })
                        connection.release()
                    }
                    else {
                        res.send({ success: true, data: '点赞成功!' });
                        connection.release()
                    }
                })
            } else {
                res.send({ success: false, data: err, msg: '已经点赞过了,不要重复点赞' });
                connection.release()
            }
        })
    })
})

//取消对某个博客的点赞
router.post('/cancelFabulous', function (req, res, next) { //username
    var param = req.body;//放到body里面
    pool.getConnection(function (err, connection) {
        connection.query('DELETE FROM blog_fabulous WHERE blogId = ? AND commitName = ?', [param.blogId, param.userName], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '点赞失败' })
                connection.release()
            } else {
                res.send({ success: true, data: '', msg: '取消成功' });
                connection.release()
            }
        })
    })

})

//查找该博客下所有评论
router.get('/findBlogCommitByBlogId', function (req, res, next) { //username
    var param = req.query;
    console.log(param, req.body)
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findBlogCommitByBlogId, [param.blogId], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查找失败' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})

//查找某人收藏的博客
router.get('/findFabByUsername', function (req, res, next) { //username
    var param = req.query;
    console.log(param, req.body)
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.findFabByUsername, [param.userName], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查找失败' });
                connection.release()
            } else {
                res.send({ success: true, data: result });
                connection.release()
            }
        })
    })
})

//增加评论
router.post('/addCommit', function (req, res, next) { //commitName,blogId,commitBody,avatar
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.addCommit, [param.commitName, param.blogId, param.commitBody, param.avatar, new Date()], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '评论失败' });
                connection.release()
            } else {
                res.send({ success: true, data: '评论成功' });
                connection.release()
            }
        })
    })
})


//上传图片接口 头像
router.post('/uploadImg', function (req, res, next) { //username
    var param = req.body;
    var imgData = req.body.imgData;
    var base64Data = imgData.replace(/^data:image\/\w+;base64,/, "");
    var dataBuffer = new Buffer(base64Data, 'base64');
    var timeStemp = new Date().getTime()
    fs.writeFile("./userAvator/" + timeStemp + ".png", dataBuffer, function (err) {
        if (err) {
            res.send(err);
        } else {
            pool.getConnection(function (err, connection) {
                connection.query('SELECT * FROM userImgApi WHERE userPhone = ?', [param.userPhone], function (err, result) {
                    if (err) {
                        res.send({ success: false, data: err, msg: '失败' });
                    } else if (result == false) { //为空的情况
                        connection.query('INSERT INTO userImgApi(userPhone,imgData) VALUES(?,?)', [param.userPhone, timeStemp], function (err, result) {
                            if (err) {
                                res.send({ success: false, data: err, msg: '失败' });
                                connection.release()
                            } else {
                                res.send({ success: false, data: timeStemp, msg: '上传成功' });
                                connection.release()
                            }
                        })
                    } else {//不为空的情况
                        connection.query('UPDATE userImgApi SET imgData = ? WHERE userPhone = ?', [timeStemp, param.userPhone], function (err, result) {
                            if (err) {
                                res.send({ success: false, data: err, msg: '失败' });
                                connection.release()
                            } else {
                                res.send({ success: false, data: timeStemp, msg: '上传成功' });
                                connection.release()
                            }
                        })
                    }
                })
            })

        }
    });
})

//更改用户头像 
router.post('/changeHeadImg', function (req, res, next) { //username
    var param = req.body;
    var timeStemp = new Date().getTime()
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.changeHeadImg, [param.headImg, param.userName, param.password], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '更改失败' });
                connection.release()
            } else if (result == false) {
                res.send({ success: false, msg: '更改失败,请检查密码' });
                connection.release()
            } else {
                res.send({ success: true, msg: '更改成功' });
                connection.release()
            }
        })
    })
})

//更改用户信息
router.post('/updatedUserInfo', function (req, res, next) { //username
    var param = req.body;
    var timeStemp = new Date().getTime()
    pool.getConnection(function (err, connection) {
        connection.query(blogSQL.updatedUserInfo, [param.nickName, param.info, param.sex, param.name, param.password], function (err, result) {
            console.log(JSON.stringify(result) + '------------------------------------------------')
            if (err) {
                res.send({ success: false, data: err, msg: '更改失败' });
                connection.release()
            } else if (result.changedRows == 0) {
                res.send({ success: false, msg: '更改失败,请检查密码' });
                connection.release()
            } else {
                res.send({ success: true, msg: '更改成功' });
                connection.release()
            }
        })
    })
})

//获取用户头像
router.get('/getImg', function (req, res, next) { //username
    var param = req.query;
    var timeStemp = new Date().getTime()
    pool.getConnection(function (err, connection) {
        connection.query('SELECT imgData FROM userImgApi WHERE userPhone = ?', [param.userPhone], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '失败' });
                connection.release()
            } else if (result == false) {
                res.send({ success: true, msg: '该用户无头像' });
                connection.release()
            } else {
                fs.readFile('./userAvator/' + result[0].imgData + '.png', "base64", function (err, data) {
                    if (err) {
                        return console.error(err);
                    }
                    res.send({ success: true, data: data });
                    connection.release()
                })
            }
        })
    })
})

//取得服务器上面的图片
router.get('/img/*', function (req, res) {
    //__dirname就是执行这行代码的地址
    //path.resolve实际上就是cd __dirname 然后再 cd ..
    console.log(req.url)
    res.sendFile(path.resolve(__dirname, '..') + req.url);
    console.log("Request for " + req.url + " received.");
})

//上传图片接口 头像
router.post('/upImg', function (req, res, next) { //username
    var param = req.body;
    var imgData = req.body.imgData;
    var base64Data = imgData.replace(/^data:image\/\w+;base64,/, "");
    var dataBuffer = new Buffer(base64Data, 'base64');
    var timeStemp = new Date().getTime()
    fs.writeFile("./img/" + timeStemp + ".png", dataBuffer, function (err) {
        if (err) {
            res.send({ success: false, data: '', msg: '上传失败' });
        } else {
            res.send({ success: true, data: 'http://dlhtx.zicp.vip:3000/img/' + timeStemp + '.png', msg: '上传成功' });
        }
    });
})


//爬虫接口
router.post('/pythonBt', function (req, res, next) { //username
    var param = req.body;
    pool.getConnection(function (err, connection) {
        connection.query('INSERT INTO bt(title,url,name) VALUES (?,?,?)', [param.title, param.url, param.name], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '失败' });
                connection.release()
            } else {
                res.send({ success: true, data: '添加成功' });
                connection.release()
            }
        })
    })
})

router.post('/testJson', function (req, res, next) { //username
    var param = req.body;
    console.log(req.body)
    res.send({ success: true, data: req.body });
})

router.post('/addFace', function (req, res, next) { //username
    var param = req.body;
    console.log(req.body)
    pool.getConnection(function (err, connection) {
        connection.query('INSERT INTO face(idCard,encoding,name) VALUES (?,?,?)', [param.idCard, param.encoding, param.name], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '插入失败' });
                connection.release()
            } else {
                res.send({ success: true, data: '添加成功' });
                connection.release()
            }
        })
    })
})
// http://dlhtx.zicp.vip:3000/face/addFace 
// {
// 	"idCard":"320123199511011218",
// 	"encoding":"123,23123,123,123,123,123",
// 	"name":"徐良俊"
// }

router.get('/getAllFace', function (req, res, next) { //username
    var param = req.body;
    console.log(req.body)
    pool.getConnection(function (err, connection) {
        connection.query('select * from face', function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查询失败' });
                connection.release()
            } else {
                var endcondingList = []
                result.forEach((item)=>{
                    console.log(item.encoding)
                    itemlist = item.encoding.split(',')
                    itemlist.forEach((item)=>{
                        item = Number(item)
                    })
                    endcondingList.push(itemlist)
                    console.log(endcondingList)
                })
                res.send({ success: true, data: endcondingList ,msg:'查询成功' });
                connection.release()
            }
        })
    })
})
//返回格式encoding要是数组['1','2','3']

router.get('/getUserInfo', function (req, res, next) { //username
    var param = req.query;
    console.log(req.query)
    pool.getConnection(function (err, connection) {
        connection.query('select * from face where face.index = ?' , [param.index], function (err, result) {
            if (err) {
                res.send({ success: false, data: err, msg: '查询失败' });
                connection.release()
            } else {
                res.send({ success: true, data: result ,msg:'查询成功' });
                connection.release()
            }
        })
    })
})

module.exports = router;